#encoding=utf-8
import psycopg2
import time
import configparser
import sys
reload(sys)
sys.setdefaultencoding('utf-8')

parking_name = ""
#------db config---------
db_host = ""
db_user = ""
db_passwd = ""
db_database = ""
db_charset = ""
db_port = ""
#-----db table config----
tableName = ""  # split by '#',example: tableA#tableB#tableC   parking_attributes_sync
#-----server config------
server_data_receiver = ""
server_heart_receiver = ""
server_error_receiver = ""
server_param_distribute = ""
#------the time to sleep of query database---------
queryTime = 5

def readConfig(): 
	global parking_name
	#------------------------
	global db_host
	global db_user
	global db_passwd
	global db_database
	global db_charset
	global db_port
	#-----db table config----
	global tableName  # split by '#',example: tableA#tableB#tableC
	#-----server config------
	global server_data_receiver
	global server_heart_receiver
	global server_error_receiver
	global server_param_distribute
	#------------------------------------------------------------
	global queryTime
	try:
		cf = configparser.ConfigParser()
		cf.read("param.conf")
		
		parking_name = cf.get("parking","parking_name").strip()
		#----------------------------------------------
		db_host = cf.get("db", "db_host").strip()
		db_user = cf.get("db", "db_user").strip()
		db_passwd = cf.get("db", "db_passwd").strip()
		db_database = cf.get("db", "db_database").strip()
		db_charset = cf.get("db", "db_charset").strip()
		db_port = cf.get("db", "db_port").strip()
		#-------------------------------------------------------------
		tableName = cf.get("tables", "tableName").strip()
		#-------------------------------------------------------------
		server_data_receiver = cf.get("server", "server_data_receiver").strip()
		server_heart_receiver = cf.get("server", "server_heart_receiver").strip()
		server_error_receiver = cf.get("server", "server_error_receiver").strip()
		server_param_distribute = cf.get("server", "server_param_distribute").strip()

		queryTime = int(cf.get("time", "queryTime").strip())
	except BaseException,e:
		print("read conf error,message:"+repr(e))
	print("\n\n\n\n\n\n\n\n***postgresql check tool by ypb***")
	print("\n\n*-----read param config success-----------")
	print("*    db_host     :"+db_host)
	print("*    db_user     :"+db_user)
	print("*    db_passwd   :"+db_passwd)
	print("*    db_database :"+db_database)
	print("*    db_charset  :"+db_charset)
	print("*    Author      :"+"ypb")
	print("*    Author email:"+"ypbck@qq.com")
	print("*-----------------------------------------")

# create database connection
def createConnection():
	times = 0
	while times<5:
		try:
			conn = psycopg2.connect(database=db_database, user=db_user, password=db_passwd, host=db_host, port=db_port)
			if(conn):
				print("get connection successed")
				print("*-----------------------------------------\n\n\n")
				return conn
		except BaseException,e:
			if(times==4):
				print("\nget connection failed,message:"+repr(e))
				sys.exit(-1)
			else:
				print("\nget connection failed,message:"+repr(e))
		times = times+1
		time.sleep(5)

# close connection
def closeConnection(conn):
	conn.close()

# query data
def query(conn,table,num):
	try:
		#print("start query")
		cursor = conn.cursor()
		cursor.execute("select * from "+table+" limit "+num)
		resList = cursor.fetchall()
		cursor.close()
		return resList
	except BaseException,e:
		print("something wrong,message:"+repr(e))
		cursor.close()
		return []

def query2(conn,sql):
	try:
		#print("start query")
		cursor = conn.cursor()
		cursor.execute(sql)
		resList = cursor.fetchall()
		cursor.close()
		return resList
	except BaseException,e:
		print("something wrong,message:"+repr(e))
		cursor.close()
		return []

def dealRes(p):
	try:
		if(p==[]):
			print("***empty table***")
			return
		for col in p:
			index = 1
			print("\n\n****************************************************")
			for v in col:
				s = str(v).replace(" ","")
				s = s.decode("string_escape")
				print(u""+str(index)+"="+s)
				index = index+1
			print("****************************************************")
		print("\n\n")
	except BaseException,e:
		print("something wrong,message:"+repr(e))

def main():
	readConfig()
	conn = createConnection()
	print("please choose the model of tool:")
	print("1.default model:you write table only for query")
	print("2.costom model:you write sql whole from query")
	model = raw_input("please enter your choose(1 or 2):")
	if(model=="1"):
		while True:
			tablename = raw_input("enter tableName which you want to check(quit is exit): ")
			if(tablename=="quit"):
				sys.exit(0)
			num = raw_input("enter a num to get the frist few record of that table: ")
			res = query(conn,tablename,num)
			dealRes(res)
	elif(model=="2"):
		while True:
			sql = raw_input("enter sql for query(quit is exit): ")
			if(sql=="quit"):
				sys.exit(0)
			res = query2(conn,sql)
			dealRes(res)

if __name__ == '__main__':
	main()
